# -*- coding: utf-8 -*-
import pymysql

from config import MYSQL

'''
pymysql基础流程。

1、先和数据库建立一个传输数据的连接通道
db = pymysql.connect(host=host,
                     user=username,
                     passwd=password,
                     port=port,
                     db=db,
                     charset="utf-8")

2、在建立数据库连接后，想要操作数据库需要使用游标Cursor，在连接没有关闭之前，游标对象可以反复使用
cursor = db.cursor()
sql = "insert into test01.user(username) values('nihao')"
cursor.execute(sql)  #用来执行sql语句,sql建议表名前写上库名。

'''

class MysqlTools:
    def __init__(self):
        mysql = MYSQL.get('TEST')   # 测试环境
        host = mysql.get('host')
        username = mysql.get('username')
        password = mysql.get('password')
        port = mysql.get('port')
        db = mysql.get('db')
        self.conn = pymysql.connect(host=host,
                                    user=username,
                                    passwd=password,
                                    port=port,
                                    db=db)

    def connect(self):
        """
        启动
        :return:
        """
        conn = self.conn
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        print("启动数据库成功!")
        return conn, cursor

    def connect_close(self, conn, cursor):
        """
        关闭
        :param conn:
        :param cursor:
        :return:
        """
        cursor.close()
        conn.close()
        print("关闭数据库成功!")

    def insert(self, sql, args, connect):
        """
        插入数据
        :param sql: 插入语句
        :return: 返回插入数据量
        """
        conn, cursor = connect
        try:
            row = cursor.execute(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()

        return row

    def insert_many(self, sql, args, connect):
        """
        批量插入数据
        :param sql: sql语句
        :param args: 插入的数据
        :return: 返回插入数据量
        """
        conn, cursor = connect
        try:
            row = cursor.executemany(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()
        return row

    def delete(self, sql, args, connect):
        """
        删除数据
        :param sql: sql语句
        :param args: 参数，一般为元组、列表或字典
        :return: 返回删除的数量
        """
        conn, cursor = connect
        try:
            row = cursor.execute(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()
        return row

    def delete_many(self, sql, args, connect):
        """
        批量删除数据
        :param sql: sql语句
        :param values: 删除数据的集合
        :return: 返回删除的数量
        """
        conn, cursor = connect
        try:
            row = cursor.executemany(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()
        return row

    def update(self, sql, args, connect):
        """
        更新数据
        :param sql: sql语句
        :param args: 参数，一般为元组、列表或字典
        :return: 返回更新的数量
        """
        conn, cursor = connect
        try:
            row = cursor.execute(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()
        return row

    def update_many(self, sql, args, connect):
        """
        批量更新数据
        :param sql: sql语句
        :param values: 参数，一般为元组、列表或字典
        :return: 返回更新的数量
        """
        conn, cursor = connect
        try:
            row = cursor.executemany(sql, args)
            conn.commit()
        except:
            row = 0
            conn.rollback()
        return row

    def select(self, sql, args, connect):
        """
        单条查询
        :param sql: 查询语句
        :param args: 参数，一般为元组、列表或字典
        :return: 返回查询结果
        """
        conn, cursor = connect
        
        cursor.execute(sql, args)
        result = cursor.fetchone()
        return result

    def select_many(self, sql, args, connect):
        """
        批量查询
        :param sql: 查询语句
        :param args: 查询参数
        :return: 返回查询结果
        """
        conn, cursor = connect

        cursor.executemany(sql, args)
        result = cursor.fetchall()
        return result

    def select_define_data(self, sql, num, values, connect):
        """
        自定义查询前xxx条数据
        :param sql: 查询sql语句
        :param num: 查询的条数
        :param values: 查询参数
        :return: 返回查询结果
        """
        conn, cursor = connect

        cursor.executemany(sql, values)
        result = cursor.fetchmany(num)
        return result


if __name__ == '__main__':
    m_db = MysqlTools()
    # 启动数据库
    conn, cursor = m_db.connect()


    # 插入单条
    # insert into 表名(列名)
    # sql = f'insert into users_msg(name, age, score) values(%s,%s,%s)'
    # result = m_db.insert(sql, ('Amy', 20, 66.2), (conn, cursor))
    # print("插入", result, "条数据！")

    # 插入多条
    # sql = f'insert into users_msg(name, age, score) values(%s,%s,%s)'
    # result = m_db.insert_many(sql, [('Lim', 20, 66.2), ('Kurt', 23, 61.2)], (conn, cursor))
    # print("插入", result, "条数据！")

    # 删除
    # sql = f'DELETE FROM users_msg WHERE id=%s'
    # result = m_db.delete(sql, (4), (conn, cursor))
    # print("删除", result, "条数据！")

    # 批量删除
    # sql = f'DELETE FROM users_msg WHERE id=%s'
    # result = m_db.delete_many(sql, (5, 2), (conn, cursor))
    # print("删除", result, "条数据！")

    # 更新
    # sql = f'UPDATE users_msg SET `name`=%s WHERE id=%s'
    # result = m_db.update(sql, ('Tot', 6), (conn, cursor))
    # print("更新", result, "条数据！")

    # 批量更新
    # sql = f'UPDATE users_msg SET `name`=%s, `score`=%s WHERE id=%s'
    # result = m_db.update_many(sql, [('Carry', 71, 6), ('Tom', 82, 7)], (conn, cursor))
    # print("更新", result, "条数据！")

    # 查询score>80的第一条数据
    sql = f'SELECT * FROM test01.users_msg WHERE score > %s'
    result = m_db.select(sql, (60,), (conn, cursor))
    print(result)

    # 查询score>60的所有数据
    # sql = f'SELECT * FROM users_msg WHERE score > %s'
    # result = m_db.select_many(sql, (60,), (conn, cursor))
    # print(result)

    # 查询score>60的前3条数据
    # sql = f'SELECT * FROM users_msg WHERE score > %s'
    # result = m_db.select_define_data(sql, 3, (60,), (conn, cursor))
    # print(result)
    
    # 关闭数据库
    m_db.connect_close(conn, cursor)
    # print(result)
